package cn.com.dashihui.kit;


import java.io.BufferedOutputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;

import com.jfinal.plugin.activerecord.Record;

public class ExcelImportUtil
 {

	/**
	 * @param response
	 * @param request
	 * @param filename	导出的文件名
	 * @param titles 标题列和列名的对应.column:列名,title标题名
	 * @param records 记录
	 */
	public static  void exportByRecord(HttpServletResponse response,HttpServletRequest request,String filename,List<Pair> titles, List<Record> records){
		exportByRecord(response, request, filename, new SheetData(titles, records));
	}

	/**
	 * @param response
	 * @param request
	 * @param filename	导出的文件名
	 * @param sheetDatas 产生一个sheet需要的数据
	 */
	public static  void exportByRecord(HttpServletResponse response,HttpServletRequest request,String filename,SheetData... sheetDatas){

		HSSFWorkbook wb = new HSSFWorkbook();								

		//标题行的style
		CellStyle titleCellStyle = wb.createCellStyle();					
		titleCellStyle.setAlignment(CellStyle.ALIGN_CENTER);				//居中
		titleCellStyle.setWrapText(true);									//自动换行	
		Font font = wb.createFont();
		font.setBoldweight(Font.BOLDWEIGHT_BOLD);						//加粗
		font.setFontName("微软雅黑");
		titleCellStyle.setFont(font);

		//内容行的style
		CellStyle cellStyle = wb.createCellStyle();	
		cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);	//垂直居中
		cellStyle.setAlignment(CellStyle.ALIGN_CENTER);//居中
		cellStyle.setWrapText(true);	
		Font font2 = wb.createFont();
		font2.setFontName("微软雅黑");
		cellStyle.setFont(font2);	
		//多张sheet
		for (SheetData sheetData : sheetDatas) {
			List<Pair> titles = sheetData.titles;
			List<Record> records = sheetData.records;
			
			HSSFSheet sheet = wb.createSheet();
			int rowIndex = 0,cellIndex=0;
			
			HSSFRow row = null;
			HSSFCell cell = null;

			//创建标题行
			row = sheet.createRow(rowIndex);
			row.setHeight((short)450);
			rowIndex++;
			
			for (Pair pair : titles) {
				
				cell = row.createCell(cellIndex);
				cell.setCellStyle(titleCellStyle);				//设置样式
				cellIndex++;
				
				cell.setCellValue(pair.title);
			}
			   Boolean isNum = false;//data是否为数值型
               Boolean isInteger=false;//data是否为整数
               Boolean isPercent=false;//data是否为百分数
               Boolean isDate=false;
			//处理每一行
			for (Record record : records) {

				row = sheet.createRow(rowIndex);
				row.setHeight((short)450);
				
				cellIndex = 0;
				  
				for (Pair pair : titles) {
					
					cell = row.createCell(cellIndex);
				//	cell.setCellStyle(cellStyle);				//设置样式
					
					
					Object value = record.get(pair.column);
					
					if(value!=null){
		                   //判断value是否为数值型
		                   isNum = value.toString().matches("^(-?\\d+)(\\.\\d+)?$");
		                   //判断value是否为整数（小数部分是否为0）
		                   isInteger=value.toString().matches("^[-\\+]?[\\d]*$");
		                   //判断value是否为百分数（是否包含“%”）
		                   isPercent=value.toString().contains("%");
		                   isDate= value.toString().matches("^d{4}-(0[1-9]|1[012])-(0[1-9]|[12][0-9]|3[01]) ([01][0-9]|2[0-3]):[0-5][0-9]:[0-5][0-9].[0-9]*$");
		                   if (isNum && !isPercent) {
		                       if (isInteger) {
		                    	   if(value.toString().getBytes().length>11){
		                    		   cell.setCellStyle(cellStyle);
				                       // 设置单元格内容为字符型
				                	   cell.setCellValue(Double.parseDouble(value.toString()));
		                    	   }else{
		                    		   cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0"));//数据格式只显示整数
			                    	   cell.setCellStyle(cellStyle);
			                    	   // 设置单元格内容为double类型
			                    	   cell.setCellValue(Double.parseDouble(value.toString()));
		                    	   }
		                       }else{
		                    	   cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0"));//保留两位小数点
		                    	   cell.setCellStyle(cellStyle);
		                    	   // 设置单元格内容为double类型
		                    	   cell.setCellValue(Double.parseDouble(value.toString()));
		                       }                   
		                   } else {
		                	   cell.setCellStyle(cellStyle);
		                       // 设置单元格内容为字符型
		                	   cell.setCellValue(value.toString());
		                   }
		                   if(isDate){
		                	   HSSFDataFormat format= wb.createDataFormat();
		                	   cellStyle.setDataFormat(format.getFormat("yyyy-MM-dd  hh:mm:ss"));
		                	   cell.setCellValue(value.toString());
		                   }
		                    int  columnWidth = sheet.getColumnWidth(cellIndex) / 256;
		                       for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {
			                           HSSFRow currentRow;
			                           //当前行未被使用过
			                           if (sheet.getRow(rowNum) == null) {
			                               currentRow = sheet.createRow(rowNum);
			                           } else {
			                               currentRow = sheet.getRow(rowNum);
			                           }
		                               HSSFCell currentCell = currentRow.getCell(cellIndex);
		                               if (currentCell.getCellType() == Cell.CELL_TYPE_STRING) {
		                                   int length = value.toString().getBytes().length;
		                                   if (columnWidth < length) {
		                                       columnWidth = length;
		                                   }
		                               }
		                               if(columnWidth>253){
		                            	   sheet.setColumnWidth(cellIndex, 254 * 256);
		                               }else{
		                            	   sheet.setColumnWidth(cellIndex, (columnWidth+4) * 256);
		                               }
		                               columnWidth = 0;
		                       }
						 cellIndex++;
					}else{
						cellIndex++;
					}
				}
				rowIndex++;
			}
		}
		
		//序号
		writeStream(filename, wb, response,request);
	}

	/**
	 * 写到输出流
	 */
	private static void writeStream(String filename, HSSFWorkbook wb, HttpServletResponse response, HttpServletRequest request)
	{

		try
		{
			String agent = request.getHeader("USER-AGENT");

			filename += ".xls";

			filename.replaceAll("/", "-");
			// filename = new String(filename.getBytes("gbk"),"ISO8859_1");
			

			if (agent.toLowerCase().indexOf("firefox")>0)
			{
				filename = new String(filename.getBytes("utf-8"), "iso-8859-1");
			}else{
				filename = URLEncoder.encode(filename, "UTF-8");
			}

			response.reset();
			response.setCharacterEncoding("UTF-8");
			response.setHeader("Content-Disposition", "attachment; filename=" + filename);
			response.setContentType("application/octet-stream;charset=UTF-8");
			OutputStream outputStream = new BufferedOutputStream(response.getOutputStream());
			wb.write(outputStream);
			outputStream.flush();
			outputStream.close();

		}
		catch (FileNotFoundException e)
		{
			e.printStackTrace();
		}
		catch (IOException e)
		{
			e.printStackTrace();
		}

	}
	
	/**
	 * 标题列和列名的对应
	 */
	public static class Pair {
		public String column;
		
		public String title;
		
		public Pair(String column,String title){
			this.column = column;
			
			this.title = title;
			
		}
	}
	
	/**
	 * 创建一个sheet需要的数据
	 */
	public static class SheetData{
		public List<Pair> titles;
		public List<Record> records;
		
		public SheetData(List<Pair> titles, List<Record> records) {
			this.titles = titles;
			
			this.records = records;
		}
	}
}

